﻿SELECT 
	'Southeast' REGION,	
	CASE STATE_CD 
	WHEN 'AL' THEN 'ALM' 
	WHEN 'LA' THEN 'ALM' 
	WHEN 'MS' THEN 'ALM' 
	WHEN 'KY' THEN 'KTNS' 
	WHEN 'TN' THEN 'KTNS' 
	WHEN 'SC' THEN 'KTNS' 
	WHEN 'NC' THEN 'KTNS' 
	ELSE STATE_CD
	END MARKET,	
	STATE_CD, 
	TRAC_ID, 
	TRAC_NUM, 
	ACCT_MGR, 
	AREA_MGR, 
	PORTFOLIO_NM, 
	PROPERTY_NM, 
	ADDRESSES, 
	GREENS, 
	SUBSCRIBERS, 
	HSIA, 
	IPTV, 
	VOIP, 
	HSIA_REVENUE, 
	IPTV_REVENUE, 
	VOIP_REVENUE, 
	OTHER_UVERSE_REVENUE, 
	TOTAL_REVENUE,
	100*GREENS /(ADDRESSES + 0.01) LIT,
	100*SUBSCRIBERS/(ADDRESSES + 0.01) INSERVICE,
	100*SUBSCRIBERS /(GREENS + 0.01)  SATURATION,
	100*IPTV_REVENUE /(TOTAL_REVENUE + 0.01) IPTVPercentage,
	100*HSIA_REVENUE /(TOTAL_REVENUE + 0.01) HSIAPercentage,
	100*VOIP_REVENUE /(TOTAL_REVENUE + 0.01)  VOIPPercentage,
	100*OTHER_UVERSE_REVENUE /(TOTAL_REVENUE + 0.01)  OtherPercentage,
	TOTAL_REVENUE /(SUBSCRIBERS + 0.01)  TotalPerSubscriber,
	IPTV_REVENUE /(IPTV + 0.01) IPTVPerSubscriber,
	HSIA_REVENUE /(HSIA + 0.01) HSIAPerSubscriber,
	VOIP_REVENUE /(VOIP + 0.01) VOIPPerSubscriber,
	OTHER_UVERSE_REVENUE /(SUBSCRIBERS + 0.01)  OtherPerSubscriber
FROM 
	SUNRISE_TEST.ACC_TRAC_ID_SUMMARY

